#!/usr/bin/python
# -*- coding: UTF-8 -*-

import requests
import url
import json
from lib.mysql import Db
import lib.func as func
import math

class run():

    dbData = {}
    currDate = ''

    bkInsterNum = 0
    bkUpdateNum = 0
    bkTotal = 0
    bkCurrDataInster = 0
    bkCurrDataUpdate = 0

    bondInsterNum = 0
    bondUpdateNum = 0
    bondTotal = 0
    bondCurrDataInster = 0
    bondCurrDataUpdate = 0


    def __init__(self):
        
        self.currDate = func.getDate()
        
        print('开始采集-------'+self.currDate)

        self.lists()

        self.getBkData()

        #完成后发送邮件
        zongjie = "可转债处理结果：总共("+str(self.bondTotal)+")，更新("+str(self.bondUpdateNum)+")，新增("+str(self.bondInsterNum)+")，"\
        "今日数据新增/更新("+str(self.bondCurrDataInster)+"/"+str(self.bondCurrDataUpdate)+")。"\
        "板块处理结果：总共("+str(self.bkTotal)+")，更新("+str(self.bkUpdateNum)+")，新增("+str(self.bkInsterNum)+")，"\
        "今日数据新增/更新("+str(self.bkCurrDataInster)+"/"+str(self.bkCurrDataUpdate)+")"

        func.sendEmail(zongjie)

    def lists(self):
        uri = url.listPages()
        req = requests.get(uri).json()
        jsonData = req['result']
        data = jsonData['data']
        totalPageNum = jsonData['pages']
        print('进入列表')

        for num in range(totalPageNum):
            page = num+1
            u = url.listPages(page)
            r = requests.get(u).json()
            d = r['result']['data']
            print("进入第"+str(page)+"页")
            for n in range(len(d)): self.__listDataInsterOrUpdate(d[n])
        
    
    def __listDataInsterOrUpdate(self, data):
        #查询是否有记录，有记录修改，没记录插入
        print("正在获取："+data['SECURITY_NAME_ABBR']+"数据")
        bondCode = data['SECURITY_CODE']
        item = Db().table('lists').select("`bond_code`=" + bondCode).find()
        bondType = data['SECUCODE'][-2:]

        bondData = {
            "bond_code":bondCode,
            "CURRENT_BOND_PRICE":data['CURRENT_BOND_PRICE'] if data['CURRENT_BOND_PRICE'] !='-' and data['CURRENT_BOND_PRICE']!=None else 100,
            "TRANSFER_PRICE":data['TRANSFER_PRICE'] if data['TRANSFER_PRICE'] !='-' and data['TRANSFER_PRICE']!=None else 0,
            "TRANSFER_PREMIUM_RATIO":data['TRANSFER_PREMIUM_RATIO'] if data['TRANSFER_PREMIUM_RATIO'] !='-' and data['TRANSFER_PREMIUM_RATIO']!=None else 0,
            "ACTUAL_ISSUE_SCALE":data['ACTUAL_ISSUE_SCALE'],
            "SECURITY_NAME_ABBR":data['SECURITY_NAME_ABBR'],
            "VALUE_DATE":data['VALUE_DATE'],
            "SECURITY_SHORT_NAME":data['SECURITY_SHORT_NAME'],
            "CONVERT_STOCK_CODE":data['CONVERT_STOCK_CODE'],
            "pinyin": func.getInitial(data['SECURITY_NAME_ABBR']),
            "type":bondType,
            "updated_at":self.currDate
            }
        
        #未上市的转债日期为空,不插入当天数据
        if data['LISTING_DATE'] != None:
            bondData['LISTING_DATE'] = data['LISTING_DATE']
            itemData = self.__getBondItemData(bondCode, bondType, data['CONVERT_STOCK_CODE'])
            bondData['j_ds'] = itemData['j_ds']
            bondData['j_bfb'] = itemData['j_bfb']
            bondData['c_l'] = itemData['c_l']
            bondData['c_e'] = itemData['c_e']

            data['j_k'] = itemData['j_k']
            data['z_s'] = itemData['z_s']
            data['max'] = itemData['max']
            data['min'] = itemData['min']
            data['spj'] = itemData['spj']
        
        bondData["options"] = json.dumps(data,ensure_ascii=False)

        #中签信息zqinfo['result']['data']
        zqInfoUrl = url.zqInfo(bondCode)
        zqinfo = requests.get(zqInfoUrl).json()
        if zqinfo['result'] != None:
            bondData['zqinfo'] = json.dumps(zqinfo['result']['data'], ensure_ascii=False)

        
        #筹资用途信息
        czInfoUrl = url.czInfo(bondCode)
        czinfo = requests.get(czInfoUrl).json()

        if czinfo['result'] != None:
            bondData['czinfo'] = json.dumps(czinfo['result']['data'], ensure_ascii=False)
        
        #重要日期信息
        zydateUrl = url.zyDate(bondCode)
        zydate = requests.get(zydateUrl).json()
        if zydate['result'] != None:
            bondData['zydate'] = json.dumps(zydate['result']['data'], ensure_ascii=False)


        if item == None:
            bondData['created_at'] = self.currDate
            lastId = Db().table('lists').inster(bondData)
            self.bondInsterNum = self.bondInsterNum + 1
            print("          成功新增转债信息")
        else:
            Db().table('lists').where("`bond_code`=" + bondCode).update(bondData)
            self.bondUpdateNum = self.bondUpdateNum + 1
            print("          成功更新转债信息")
        
        print("          完成！")

        self.bondTotal = self.bondTotal + 1

    #获取转债当天的成交数据
    #bond_code:转债代码
    #bond_type:转债所属市场 上市（SH）或深市（SZ）
    #code:正股代码
    def __getBondItemData(self, bond_code, bond_type, code):
        # bondType = '0'
        # if bond_type == 'SH':
        #     bondType = '1'

        
        # uri = url.bondItemData+'&secid='+bondType+'.'+bond_code
        uri = url.bondItemDataUrl(bond_code, bond_type)
        req = requests.get(uri)

        jsonData = req.json()

        data = jsonData['data']

        whereTime = func.getDate(1)

        #查询当前是否有录入数据
        rec = Db().table('prices').select("`created_at` >='"+ whereTime +"' and `bond_code`="+bond_code).find()

        if data != None:
            bondData = {
                "bond_code":bond_code,
                "j_k":data['f46'],
                "z_s":data['f60'],
                "max":data['f44'],
                "min":data['f45'],
                "spj":data['f43'],
                "c_l":data['f47'],
                "c_e":data['f48'],
                "updated_at":self.currDate
            }
        else:
            bondData = {
                "bond_code":bond_code,
                "j_k":0,#今开
                "z_s":0,#昨收
                "max":0,#最高价
                "min":0,#最低价
                "spj":0,#收盘价
                "c_l":0,#成交量
                "c_e":0,#成交额
                "updated_at":self.currDate
            }

        if rec == None:
            bondData['created_at'] = self.currDate
            lastId = Db().table('prices').inster(bondData)
            self.bondCurrDataInster = self.bondCurrDataInster + 1
            print("          成功新增当天数据！")
        else:
            Db().table('prices').where("`bond_code`=" + bond_code + " and `created_at` >='"+ whereTime +"'").update(bondData)
            self.bondCurrDataUpdate = self.bondCurrDataUpdate + 1
            print("          成功更新当天数据！")

        if data != None:
            bondData['j_ds'] = data['f169']
            bondData['j_bfb'] = data['f170']
        else:
            bondData['j_ds'] = 0
            bondData['j_bfb'] = 0

        
        #更新板块转债关联关系
        self.__bondGlBk(bond_code, bond_type, code)

        return bondData

    #转债所属板块
    def __bondGlBk(self, bond_code, bond_type, code):
        uri = url.ssbkUrl(code, bond_type)
        req = requests.get(uri).json()
        
        data = req['ssbk']

        codeArr = []
        for num in range(len(data)):
            bk_code = data[num]['BOARD_CODE']
            codeArr.append(bk_code)
        
        codeStr = ','.join(codeArr)

        isExist = Db().table('bkgls').where("`bond_code`=" + bond_code).find()
        glData = {
            "bond_code":bond_code,
            "bk_code":codeStr,
            "updated_at":self.currDate
        }
        if isExist == None:
            glData['created_at'] = self.currDate
            Db().table('bkgls').inster(glData)
            print("          成功添加板块关联关系")
        else:
            Db().table('bkgls').where("`bond_code`=" + bond_code).update(glData)
            print("          成功更新板块关联关系")
        
        

    #获取板块数据
    def getBkData(self):
        #循环三大板块
        for num in range(3):
            bkType = num+1
            uri = url.bksUrl(bkType, 1)
            req = requests.get(uri).json()
            data = req['data']
            #获取总页数
            totalPages = math.ceil(data['total'] / 20)
            print("板块数据："+ str(bkType))
            self.__bkPageData(totalPages, bkType)
            print('完成所有板块数据处理！')

        
    #获取一个板块数据
    def __bkPageData(self, totalPages, bkType):

        for num in range(totalPages):
            page = num+1
            uri = url.bksUrl(bkType, page)
            req = requests.get(uri).json()

            print("进入板块："+str(bkType)+" - "+ str(page))
            data = req['data']
            dataLen = len(data['diff'])

            for k in range(dataLen):
                bkdata = data['diff'][k]
                #板块代码 
                #int 主要为了消掉0。例如：0428
                bkCode = int(bkdata['f12'].replace('BK',''))
                bkCode = str(bkCode)

                #乘以100 转换整数，使用时再除以100
                j_k = int(bkdata['f17']*100)
                z_s = int(bkdata['f18']*100)
                zmax = int(bkdata['f15']*100)
                zmin = int(bkdata['f16']*100)
                spj = int( bkdata['f2']*100)
                c_l = bkdata['f5']
                c_e = bkdata['f6']

                bkds2 = int(bkdata['f4']*100)
                bkds3 = int(bkdata['f3']*100)

                bkname = bkdata['f14']
                piny = func.getInitial(bkname)

                print('获取板块：'+ bkname + ' 数据')

                bondCount = Db().table('lists').where("bond_code in (SELECT bond_code FROM bkgls WHERE bk_code LIKE '%"+bkCode+"%') AND c_l > 0").fields('count(id) as num').find()
                
                saveData = {
                    "bk_code":int(bkCode),
                    "bk_name":bkname,
                    "bk_ds":spj,
                    "bk_ds2":bkds2,
                    "bk_ds3":bkds3,
                    "pinyin":piny,
                    "type":bkType,
                    "bond_count":int(bondCount['num']),
                    "updated_at":self.currDate
                }

                if int(bondCount['num']) > 0:
                    bondInfo = Db().table('lists')\
                    .where("bond_code in (SELECT bond_code FROM bkgls WHERE bk_code LIKE '%"+bkCode+"%') AND c_l > 0 ORDER BY j_bfb DESC")\
                    .fields('bond_code,j_bfb,SECURITY_NAME_ABBR as bond_name').find()

                    bondUp = Db().table('lists').where("bond_code in (SELECT bond_code FROM bkgls WHERE bk_code LIKE '%"+bkCode+"%') AND c_l > 0 AND j_ds > 0").fields('count(id) as num').find()
                    bondDown = Db().table('lists').where("bond_code in (SELECT bond_code FROM bkgls WHERE bk_code LIKE '%"+bkCode+"%') AND c_l > 0 AND j_ds < 0").fields('count(id) as num').find()

                    saveData['bond_code'] = bondInfo['bond_code']
                    saveData['bond_name'] = bondInfo['bond_name']
                    saveData['bond_j_bfb'] = bondInfo['j_bfb']
                    saveData['bond_up'] = bondUp['num']
                    saveData['bond_down'] = bondDown['num']

                bkIsExist = Db().table('bks').select("`bk_code`="+bkCode).find()

                if bkIsExist == None:
                    saveData['created_at'] = self.currDate
                    Db().table('bks').inster(saveData)
                    self.bkInsterNum = self.bkInsterNum + 1
                    print('          新增板块数据成功！')
                else:
                    Db().table('bks').where("`bk_code`="+bkCode).update(saveData)
                    self.bkUpdateNum = self.bkUpdateNum + 1
                    print('          更新板块数据成功！')
                
                whereTime = func.getDate(1)
                bkItemDataIsExist = Db().table('bkdatas').where("`bk_code`="+bkCode+" and `created_at`>='"+whereTime+"'").find()
                bkItemData = {
                    "bk_code":int(bkCode),
                    "j_k": j_k,
                    "z_s": z_s,
                    "max": zmax,
                    "min": zmin,
                    "spj":spj,
                    "c_l":c_l,
                    "c_e":c_e,
                    "updated_at":self.currDate
                }
                if bkItemDataIsExist == None:
                    bkItemData['created_at'] = self.currDate
                    Db().table('bkdatas').inster(bkItemData)
                    self.bkCurrDataInster = self.bkCurrDataInster + 1
                    print('          新增当天板块数据成功！')
                else:
                    Db().table('bkdatas').where("`bk_code`="+bkCode+" and `created_at`>='"+whereTime+"'").update(bkItemData)
                    self.bkCurrDataUpdate = self.bkCurrDataUpdate + 1
                    print('          更新当天板块数据成功！')

                print('          完成！')

                self.bkTotal = self.bkTotal + 1






        
    




